package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.sql.DataSource;

import fact.view.DTO.EntrySetDTO;
import fact.view.DTO.NodeHierarchyDTO;
import fact.view.DTO.ScenarioDTO;
import fact.view.util.FacesUtils;

@ManagedBean(name = "entrySetModelBean")
//@RequestScoped
@ViewScoped
public class EntrySetModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<EntrySetDTO> getEntrySetList(int month,int year) throws SQLException
	{
		ArrayList<EntrySetDTO> entrySetList=new ArrayList<EntrySetDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("SELECT   tmp2.* " +
				"    FROM (SELECT tmp1.*, entry_sets.entry_set_id,status " +
				"            FROM (SELECT mst_makes.make_id, mst_makes.make_name, " +
				"                         mst_markets.market_id, mst_markets.market_name, " +
				"                         mst_segments.* " +
				"                    FROM make_market_combination, " +
				"                         mst_makes, " +
				"                         mst_markets, " +
				"                         mst_segments " +
				"                   WHERE make_market_combination.make_id = mst_makes.make_id " +
				"                     AND make_market_combination.market_id = " +
				"                                                         mst_markets.market_id) tmp1 " +
				"                 LEFT OUTER JOIN " +
				"                 entry_sets " +
				"                 ON tmp1.make_id = entry_sets.make_id " +
				"               AND tmp1.market_id = entry_sets.market_id " +
				"               AND tmp1.segment_id = entry_sets.segment_id " +
				"               AND MONTH = ? " +
				"               AND YEAR = ? " +
				"                 ) tmp2 " +
				"         LEFT OUTER JOIN " +
				"         mst_segments ON tmp2.segment_id = mst_segments.segment_id " +
				"ORDER BY tmp2.make_id, market_id, tmp2.segment_id "
);
		
		ps.setInt(1, month);
		ps.setInt(2, year);
		ResultSet result =  ps.executeQuery();
		
		EntrySetDTO es=null;
				while(result.next())
				{
					es=new EntrySetDTO();
					
					es.setEntrySetId(result.getInt("ENTRY_SET_ID"));
					es.setMakeId(result.getInt("MAKE_ID"));
					es.setMakeName(result.getString("MAKE_NAME"));
					es.setMarketId(result.getInt("MARKET_ID"));
					es.setMarketName(result.getString("MARKET_NAME"));
					
					es.setSegmentId(result.getInt("SEGMENT_ID"));
					es.setSegmentName(result.getString("SEGMENT_NAME"));
					
					es.setStatus(result.getString("STATUS"));
					
					entrySetList.add(es);
				}
		
		
		return entrySetList;
	}
	
	public boolean createEntrySet(EntrySetDTO entrySet,ArrayList<NodeHierarchyDTO> nodeList,ArrayList<ScenarioDTO> scenarioList) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		int entrySetId=0;
		PreparedStatement ps = con.prepareStatement("Select SQN_ENTRYSETS.NEXTVAL from dual");  

		ResultSet rs =  ps.executeQuery();
		if(rs.next())
			entrySetId=rs.getInt(1);
		
		ps = con.prepareStatement("Insert into ENTRY_SETS(ENTRY_SET_ID,MAKE_ID,MARKET_ID,SEGMENT_ID, " +
				 "MONTH,YEAR,STATUS) Values(?,?,?,?,?,?,'START')");  
		
		ps.setInt(1, entrySetId);
		ps.setInt(2, entrySet.getMakeId());
		ps.setInt(3, entrySet.getMarketId());
		ps.setInt(4, entrySet.getSegmentId());
		ps.setInt(5, entrySet.getMonth());
		ps.setInt(6, entrySet.getYear());
		int result =  ps.executeUpdate();
		
		for(int i=0;i<nodeList.size();i++)
		{
			for(int j=0;j<scenarioList.size();j++)
			{
				ps = con.prepareStatement("Insert into NODE_FACTS(ENTRY_SET_ID,NODE_ID,SCENARIO_ID)  " +
						" Values(?,?,?)");  
				ps.setInt(1, entrySetId);
				ps.setInt(2, nodeList.get(i).getNodeId());
				ps.setInt(3, scenarioList.get(j).getScenarioId());
				ps.executeUpdate();
			}
		}
		
		FacesUtils.addInfoMessage(null,"Entry Set Successfully Added.");
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	
	public boolean batchEntrySet(ArrayList<EntrySetDTO> entrySetList,ArrayList<NodeHierarchyDTO> nodeList,ArrayList<ScenarioDTO> scenarioList,int month,int year) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		int entrySetId=0;
		int result=0;
		
		for(int e=0;e<entrySetList.size();e++)
		{
		EntrySetDTO entrySet=entrySetList.get(e);
		if(entrySet.getSelected()!=null && entrySet.getSelected().equalsIgnoreCase("true"))
			
		{
		PreparedStatement ps = con.prepareStatement("Select SQN_ENTRYSETS.NEXTVAL from dual");  

		ResultSet rs =  ps.executeQuery();
		if(rs.next())
			entrySetId=rs.getInt(1);
		
		ps = con.prepareStatement("Insert into ENTRY_SETS(ENTRY_SET_ID,MAKE_ID,MARKET_ID,SEGMENT_ID, " +
				 "MONTH,YEAR,STATUS) Values(?,?,?,?,?,?,'START')");  
		
		ps.setInt(1, entrySetId);
		ps.setInt(2, entrySet.getMakeId());
		ps.setInt(3, entrySet.getMarketId());
		ps.setInt(4, entrySet.getSegmentId());
		ps.setInt(5, month);
		ps.setInt(6, year);
		result =  ps.executeUpdate();
		
		for(int i=0;i<nodeList.size();i++)
		{
			for(int j=0;j<scenarioList.size();j++)
			{
				ps = con.prepareStatement("Insert into NODE_FACTS(ENTRY_SET_ID,NODE_ID,SCENARIO_ID)  " +
						" Values(?,?,?)");  
				ps.setInt(1, entrySetId);
				ps.setInt(2, nodeList.get(i).getNodeId());
				ps.setInt(3, scenarioList.get(j).getScenarioId());
				ps.executeUpdate();
			}
		}
		}
		}
		
		FacesUtils.addInfoMessage(null,"Entry Set Successfully Added.");
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	

}
